﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_CAR_REPORT_Car2013r_Get_Monthly_Cost_Summary')
	BEGIN
		DROP Procedure usp_UPDMS_CAR_REPORT_Car2013r_Get_Monthly_Cost_Summary
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_CAR_REPORT_Car2013r_Get_Monthly_Cost_Summary
**	Desc : 월간차량비용집계
**	Test Exec Query : Exec usp_UPDMS_CAR_REPORT_Car2013r_Get_Monthly_Cost_Summary 'CAR01', '2010-06', 'ko'
**	Called by : Car_Dac_UPDMS_CAR_REPORT_Car2013r.cs
**	Program ID : Car2013r
**	Auth : 송시명
**	Date : 2010-07-13
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_CAR_REPORT_Car2013r_Get_Monthly_Cost_Summary]
@ls_car_id nvarchar(5),
@ls_base_ym nvarchar(7),
@ls_lang_set nvarchar(2)

AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

SELECT dbo.ufn_UPDMS_Get_Code_Name(ucc.Cd, 'CAR', @ls_lang_set) AS Div_Nm,
       ISNULL(rslt.Amount, 0) AS Amount
  FROM UPDMS_COM_CD AS ucc WITH(NOLOCK)
  LEFT OUTER JOIN 
     (
       SELECT Div,
              SUM(Amount) AS Amount
         FROM (
              --주유
              SELECT 'AH001' AS Div,
                     Amount
                FROM UPDMS_CAR_REFUELLING WITH(NOLOCK)
               WHERE Car_Id = @ls_car_id
                 AND LEFT(Refuelling_Dt, 7) = @ls_base_ym
              UNION ALL
              --정비
              SELECT 'AH002',
                     Amount
                FROM UPDMS_CAR_SERVICE WITH(NOLOCK)
               WHERE Car_Id = @ls_car_id
                 AND Div <> 'AD003'
                 AND LEFT(Service_Dt, 7) = @ls_base_ym
              UNION ALL
              --주차,통행료(카드)
              SELECT 'AH003',
                     Amount
                FROM UPDMS_CRD_ITEM_BIZ WITH(NOLOCK)
               WHERE Account = 'AC024' 
                 AND LEFT(Biz_Dt, 7) = @ls_base_ym
              UNION ALL
              --주차,통행료(현금)
              SELECT 'AH003',
                     Amount
                FROM UPDMS_MNY_IN_OUT_MGT WITH(NOLOCK)
               WHERE Account = 'AC024'
                 AND LEFT(Reg_Dt, 7) = @ls_base_ym
              UNION ALL
              --세차
              SELECT 'AH004',
                     Amount
                FROM UPDMS_CAR_SERVICE WITH(NOLOCK)
               WHERE Car_Id = @ls_car_id
                 AND Div = 'AD003'
                 AND LEFT(Service_Dt, 7) = @ls_base_ym
             ) x
         GROUP BY Div
     ) rslt
    ON ucc.Cd = rslt.Div
 WHERE ucc.Id = 'CAR'
   AND ucc.Cd LIKE 'AH%'
   AND ucc.Use_Yn = 'Y'

GO
